/*
 * ============================================================================
 *
 *  [THC RPG] Total HardCore RPG
 *
 *  File:          sql.inc
 *  Type:          Core
 *  Description:   contains SQL specific functions
 *
 *  Copyright (C) 2009-2011  ArsiRC
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 * ============================================================================
 */

#pragma semicolon 1

new connectcounter = 0;

ConnectSQL()
{
    if (hSQL != INVALID_HANDLE)
        CloseHandle(hSQL);
    hSQL = INVALID_HANDLE;

    if(SQL_CheckConfig("thc_rpg"))
        SQL_TConnect(ConnectSQLCallback, "thc_rpg");
    else
        LogMgr_Print(g_moduleCore, LogType_Fatal_Plugin, "ConnectSQL", "No config entry found for 'thc_rpg' in databases.cfg");
}

public ConnectSQLCallback(Handle:owner, Handle:hndl, const String:error[], any:data)
{
    connectcounter++;

    if (connectcounter >= 5)
    {
        LogMgr_Print(g_moduleCore, LogType_Fatal_Plugin, "ConnectSQL", "PLUGIN STOPPED - reconnect counter reached max - PLUGIN STOPPED");
        return -1;
    }

    if (hndl == INVALID_HANDLE)
    {
        LogMgr_Print(g_moduleCore, LogType_Error, "ConnectSQL", "Connection to SQL database has failed, Reason: %s", error);
        ConnectSQL();
        return -1;
    }

    LogMgr_Print(g_moduleCore, LogType_Debug, "ConnectSQL", "Successfully connected to database");

    new String:driver[16];
    SQL_GetDriverIdent(owner,driver,sizeof(driver));
    if(StrEqual(driver,"mysql",false))
        SQL_FastQuery(hndl,"SET NAMES 'utf8'");

    // save database handle
    hSQL = CloneHandle(hndl);

    return 1;
}

ManageSQLTable(mode)
{
    // Check if the table exists
    SQL_TQuery(hSQL, ManageSQLTableCallback, "SHOW TABLES", mode, DBPrio_High);
}

public ManageSQLTableCallback(Handle:owner, Handle:hndl, const String:error[], any:mode)
{
    if (owner == INVALID_HANDLE)
    {
        LogMgr_Print(g_moduleCore, LogType_Error, "ManageSQLTable", "Connection to SQL database has failed, Reason: %s", error);
        ConnectSQL();
        return -1;
    }

    if (hndl == INVALID_HANDLE)
        LogMgr_Print(g_moduleCore, LogType_Fatal_Plugin, "ManageSQLTable", "Error at requesting tables, Reason: %s", error);
    else
    {
        SQL_Rewind(hndl);
        while (SQL_FetchRow(hndl))
        {
            decl String:result[16];
            SQL_FetchString(hndl,0,result,sizeof(result));
            new exists=StrContains(result, "thc_rpg", false);
            if(exists!=-1)
            {
                if(mode==2)
                    SQL_TQuery(hSQL,DropSQLTableCallback,"DROP TABLE thc_rpg", DBPrio_High);
            }
            else
            {
                if(mode==1)
                    SQL_TQuery(hSQL,CreateSQLTableCallback,"CREATE TABLE thc_rpg (ID VARCHAR(64),name VARCHAR(128),xp INTEGER(8),reqxp INTEGER(8),level INTEGER(8),credits INTEGER(8),dbver VARCHAR(8))", DBPrio_High);
            }
        }
    }

    return 1;
}

public CreateSQLTableCallback(Handle:owner, Handle:hndl, const String:error[], any:data)
{
    if (owner == INVALID_HANDLE)
    {
        LogMgr_Print(g_moduleCore, LogType_Error, "CreateSQLTable", "Connection to SQL database has failed, Reason: %s", error);
        ConnectSQL();
        return -1;
    }

    if (hndl == INVALID_HANDLE)
        LogMgr_Print(g_moduleCore, LogType_Fatal_Plugin, "CreateSQLTable", "Could not create the necessary table in database, Reason: %s", error);
    else
    {
        LogMgr_Print(g_moduleCore, LogType_Debug, "CreateSQLTable", "Successfully created necessary table in database");

        for(new moduleindex=1;moduleindex<MODULE_COUNT;moduleindex++)
        {
            decl String:shortname[16], String:sLevel[16], String:sCost[16];
            // Read moduleindex as a Module type.
            new Module:upgrade = Module:moduleindex;

            ModuleMgr_ReadString(upgrade, ModuleData_ShortName, shortname, sizeof(shortname));
            Format(sLevel,sizeof(sLevel),"%s_l",shortname);
            CreateColumn(owner,sLevel,"VARCHAR(16)");
            Format(sCost,sizeof(sCost),"%s_c",shortname);
            CreateColumn(owner,sCost,"VARCHAR(16)");
        }
    }

    return 1;
}

public DropSQLTableCallback(Handle:owner, Handle:hndl, const String:error[], any:data)
{
    if (owner == INVALID_HANDLE)
    {
        LogMgr_Print(g_moduleCore, LogType_Error, "DropSQLTable", "Connection to SQL database has failed, Reason: %s", error);
        ConnectSQL();
        return -1;
    }

    if (hndl == INVALID_HANDLE)
        LogMgr_Print(g_moduleCore, LogType_Error, "DropSQLTable", "Could not drop tabel from database, Reason: %s", error);
    else
        LogMgr_Print(g_moduleCore, LogType_Debug, "DropSQLTable", "Successfully dropped table from database");

    return 1;
}

InsertPlayer(client)
{
    if(client)
    {
        decl String:uniqueid[64];
        new vecposi = GetPlayerVectorPosition(client);
        if(vecposi!=-1)
            GetArrayString(GetArrayCell(vecPlayers,vecposi), VECTOR_PLAYERS_ID, uniqueid, sizeof(uniqueid));
        else
            return -1;

        decl String:buffer[8192],String:clientname[128];
        GetClientName(client,clientname,sizeof(clientname));
        ReplaceString(clientname, sizeof(clientname), "'", "", false);

        Format(buffer,sizeof(buffer),"INSERT INTO thc_rpg (ID,name,xp,reqxp,level,credits,dbver");
        for(new moduleindex=1;moduleindex<MODULE_COUNT;moduleindex++)
        {
            decl String:shortname[16];
            // Read moduleindex as a Module type.
            new Module:upgrade = Module:moduleindex;

            ModuleMgr_ReadString(upgrade, ModuleData_ShortName, shortname, sizeof(shortname));
            Format(buffer,sizeof(buffer),"%s,%s_l,%s_c",buffer,shortname,shortname);
        }
        Format(buffer,sizeof(buffer),"%s) VALUES ('%s','%s','0','%d','1','%d','%s'",buffer,uniqueid,clientname,g_CoreConfigCache[CoreConfig_exp_start],g_CoreConfigCache[CoreConfig_credits_start],PROJECT_VERSION);
        SetStaticXP(client,0);
        SetStaticReqXP(client,g_CoreConfigCache[CoreConfig_exp_start]);
        SetStaticLevel(client,1);
        SetStaticCredits(client,g_CoreConfigCache[CoreConfig_credits_start]);
        for(new moduleindex=1;moduleindex<MODULE_COUNT;moduleindex++)
        {
            decl String:shortname[16];
            // Read moduleindex as a Module type.
            new Module:upgrade = Module:moduleindex;

            ModuleMgr_ReadString(upgrade, ModuleData_ShortName, shortname, sizeof(shortname));
            new cost = ModuleMgr_ReadCell(upgrade, ModuleData_Cost);

            Format(buffer,sizeof(buffer),"%s,'0','%d'",buffer,cost);
            SetPlayerUpgradeLevel(client,upgrade,0);
            SetPlayerUpgradeCost(client,upgrade,cost);
        }
        Format(buffer,sizeof(buffer),"%s)",buffer);
        if(strlen(buffer)>=sizeof(buffer))
            LogMgr_Print(g_moduleCore, LogType_Error, "InsertPlayer", "Player %s not added to database, Reason: string size limit exceeded", clientname);
        else
            SQL_TQuery(hSQL, InsertPlayerCallback, buffer, client, DBPrio_High);

        return 1;
    }
    else
        return -1;
}

public InsertPlayerCallback(Handle:owner, Handle:hndl, const String:error[], any:client)
{
    if (owner == INVALID_HANDLE)
    {
        LogMgr_Print(g_moduleCore, LogType_Error, "InsertPlayer", "Connection to SQL database has failed, Reason: %s", error);
        ConnectSQL();
        return -1;
    }

    decl String:clientname[128];
    GetClientName(client,clientname,sizeof(clientname));
    ReplaceString(clientname, sizeof(clientname), "'", "", false);

    if (hndl == INVALID_HANDLE)
        LogMgr_Print(g_moduleCore, LogType_Error, "InsertPlayer", "Player %s not added to database, Reason: %s", clientname, error);
    else
        LogMgr_Print(g_moduleCore, LogType_Debug, "InsertPlayer", "Player %s added to database", clientname);

    return 1;
}

RemovePlayer(client)
{
    if(client)
    {
        new vecPosi = GetPlayerVectorPosition(client);
        if(vecPosi!=-1)
        {
            decl String:uniqueid[64],String:buffer[128];

            GetArrayString(GetArrayCell(vecPlayers,vecPosi), VECTOR_PLAYERS_ID, uniqueid, sizeof(uniqueid));
            Format(buffer,sizeof(buffer),"DELETE FROM thc_rpg WHERE ID=%s",uniqueid);
            SQL_TQuery(hSQL, RemovePlayerCallback, buffer, client, DBPrio_High);
            return 1;
        }
        else
            return -1;
    }
    else
        return -1;
}

public RemovePlayerCallback(Handle:owner, Handle:hndl, const String:error[], any:client)
{
    if (owner == INVALID_HANDLE)
    {
        LogMgr_Print(g_moduleCore, LogType_Error, "RemovePlayer", "Connection to SQL database has failed, Reason: %s", error);
        ConnectSQL();
        return -1;
    }

    decl String:clientname[128];
    GetClientName(client,clientname,sizeof(clientname));
    ReplaceString(clientname, sizeof(clientname), "'", "", false);

    if (hndl == INVALID_HANDLE)
        LogMgr_Print(g_moduleCore, LogType_Error, "RemovePlayer", "Player %s not removed from database, Reason: %s", clientname, error);
    else
        LogMgr_Print(g_moduleCore, LogType_Debug, "RemovePlayer", "Player %s removed from database", clientname);

    return 1;
}

LoadPlayerData(client)
{
    if(client)
    {
        decl String:uniqueid[64];
        new vecPosi=GetPlayerVectorPosition(client);
        if(vecPosi!=-1)
            GetArrayString(GetArrayCell(vecPlayers,vecPosi), VECTOR_PLAYERS_ID, uniqueid, sizeof(uniqueid));
        else
            return -1;
        decl String:buffer[128];
        Format(buffer,sizeof(buffer),"SELECT * FROM thc_rpg WHERE ID = '%s'",uniqueid);
        SQL_TQuery(hSQL, LoadPlayerDataCallback, buffer, client, DBPrio_High);
        return 1;
    }
    else
        return -1;
}

public LoadPlayerDataCallback(Handle:owner, Handle:hndl, const String:error[], any:client)
{
    if (owner == INVALID_HANDLE)
    {
        LogMgr_Print(g_moduleCore, LogType_Error, "LoadPlayerData", "Connection to SQL database has failed, Reason: %s", error);
        ConnectSQL();
        return -1;
    }

    decl String:clientname[128];
    GetClientName(client,clientname,sizeof(clientname));
    ReplaceString(clientname, sizeof(clientname), "'", "", false);

    if (hndl == INVALID_HANDLE)
    {
        LogMgr_Print(g_moduleCore, LogType_Error, "LoadPlayerData", "Cant load data of Player %s from database, Reason: %s", clientname, error);
    }
    else
    {
            SQL_Rewind(hndl);
            new bool:fetch=SQL_FetchRow(hndl);
            if(!fetch)
            {
                LogMgr_Print(g_moduleCore, LogType_Debug, "LoadPlayerData", "Player %s does not exists in database, needs to be inserted", clientname);
                InsertPlayer(client);
                return 0;
            }
            else
            {
                SetStaticXP(client,GetSQLDataInt(hndl,"xp"));
                SetStaticReqXP(client,GetSQLDataInt(hndl,"reqxp"));
                SetStaticLevel(client,GetSQLDataInt(hndl,"level"));
                SetStaticCredits(client,GetSQLDataInt(hndl,"credits"));
                for(new moduleindex=1;moduleindex<MODULE_COUNT;moduleindex++)
                {
                    decl String:shortname[16];
                    // Read moduleindex as a Module type.
                    new Module:upgrade = Module:moduleindex;

                    ModuleMgr_ReadString(upgrade, ModuleData_ShortName, shortname, sizeof(shortname));

                    decl String:buffer[128];
                    Format(buffer,sizeof(buffer),"%s_l",shortname);
                    new level=GetSQLDataInt(hndl,buffer);
                    if(level==-1)
                        level = 0;
                    SetPlayerUpgradeLevel(client,upgrade,level);

                    Format(buffer,sizeof(buffer),"%s_c",shortname);
                    new cost=GetSQLDataInt(hndl,buffer);
                    if(cost==-1)
                        cost = ModuleMgr_ReadCell(upgrade, ModuleData_Cost);
                    SetPlayerUpgradeCost(client,upgrade,cost);
                }
            }

    }

    return 1;
}

SavePlayerData(client)
{
    if(client)
    {
        decl String:uniqueid[64];
        new vecPosi=GetPlayerVectorPosition(client);
        if(vecPosi!=-1)
            GetArrayString(GetArrayCell(vecPlayers,vecPosi), VECTOR_PLAYERS_ID, uniqueid, sizeof(uniqueid));
        else
            return -1;

        decl String:clientname[128];
        GetClientName(client,clientname,sizeof(clientname));
        ReplaceString(clientname, sizeof(clientname), "'", "", false);

        decl String:buffer[2048];
        Format(buffer,sizeof(buffer),"name = '%s', xp = '%d', reqxp = '%d', level = '%d', credits = '%d'",clientname,GetXP(client),GetReqXP(client),GetLevel(client),GetCredits(client));
        for(new moduleindex=1;moduleindex<MODULE_COUNT;moduleindex++)
        {
            decl String:shortname[16];
            // Read moduleindex as a Module type.
            new Module:upgrade = Module:moduleindex;

            ModuleMgr_ReadString(upgrade, ModuleData_ShortName, shortname, sizeof(shortname));
            Format(buffer,sizeof(buffer),"%s, %s_l = '%d', %s_c = '%d'",buffer,shortname,GetPlayerUpgradeLevel(client,upgrade),shortname,GetPlayerUpgradeCost(client,upgrade));
        }
        decl String:query[2048];
        Format(query,sizeof(query),"UPDATE thc_rpg SET %s WHERE ID = '%s'",buffer,uniqueid);
        SQL_TQuery(hSQL, SavePlayerDataCallback, query, client, DBPrio_High);
        return vecPosi;
    }
    else
        return -1;
}

public SavePlayerDataCallback(Handle:owner, Handle:hndl, const String:error[], any:client)
{
    if (owner == INVALID_HANDLE)
    {
        LogMgr_Print(g_moduleCore, LogType_Error, "SavePlayerData", "Connection to SQL database has failed, Reason: %s", error);
        ConnectSQL();
        return -1;
    }

    decl String:clientname[128];
    GetClientName(client,clientname,sizeof(clientname));
    ReplaceString(clientname, sizeof(clientname), "'", "", false);

    if (hndl == INVALID_HANDLE)
        LogMgr_Print(g_moduleCore, LogType_Error, "SavePlayerData", "Player %s not saved to database, Reason: %s", clientname, error);
    else
        LogMgr_Print(g_moduleCore, LogType_Debug, "SavePlayerData", "Player %s saved to database", clientname);

    return 1;
}

//
//
//

GetSQLDataInt(Handle:query,const String:columnname[])
{
    new column;
    decl String:result[16];
    SQL_FieldNameToNum(query,columnname,column);
    SQL_FetchString(query,column,result,sizeof(result));
    if(SQL_IsFieldNull(query, column))
        return -1;
    else
        return StringToInt(result);
}

CreateColumn(Handle:sql,const String:columnname[],const String:settings[])
{
    decl String:query[256];

    Format(query,sizeof(query),"SELECT %s FROM thc_rpg",columnname);
    if(!SQL_FastQuery(hSQL,query))
    {
        Format(query,sizeof(query),"ALTER TABLE thc_rpg ADD COLUMN %s %s",columnname,settings);
        if(SQL_FastQuery(sql,query))
            LogMgr_Print(g_moduleCore, LogType_Debug, "CreateColumn", "Successfully created necessary column '%s' in database", columnname);
        else
        {
            decl String:errorbuffer[1024];
            new bool:error=SQL_GetError(hSQL, errorbuffer, sizeof(errorbuffer));
            if(error)
                LogMgr_Print(g_moduleCore, LogType_Fatal_Plugin, "CreateColumn", "Could not create column %s in database, Reason: %s", columnname, errorbuffer);
        }
    }
}

GetPlayerRank(client)
{
    if(client)
    {
        new rank,Handle:hResults;
        decl String:buffer[128];

        Format(buffer,sizeof(buffer),"SELECT COUNT(*) FROM thc_rpg WHERE level > %d",GetLevel(client));
        hResults=SQL_Query(hSQL,buffer);
        if(SQL_FetchRow(hResults))
            rank+=SQL_FetchInt(hResults,0);

        Format(buffer, sizeof(buffer), "SELECT COUNT(*) FROM thc_rpg WHERE level = %d AND xp > %d",GetLevel(client),GetXP(client));
        hResults=SQL_Query(hSQL,buffer);
        if(SQL_FetchRow(hResults))
            rank+=SQL_FetchInt(hResults,0);

        Format(buffer, sizeof(buffer), "SELECT COUNT(*) FROM thc_rpg WHERE level = %d AND xp = %d AND credits > %d",GetLevel(client),GetXP(client),GetCredits(client));
        hResults=SQL_Query(hSQL,buffer);
        if(SQL_FetchRow(hResults))
            rank+=SQL_FetchInt(hResults,0);

        CloseHandle(hResults);
        return rank+1;
    }
    else
        return -1;
}

GetPlayerTop10(String:Top10[][][],maxlen)
{
    new Handle:hResults,column1,column2,playercount;
    decl String:buffer[128];

    Format(buffer,sizeof(buffer),"SELECT COUNT(*) FROM thc_rpg ORDER BY level DESC, xp DESC, credits DESC LIMIT 10");
    hResults=SQL_Query(hSQL,buffer);
    if(SQL_FetchRow(hResults))
        playercount=SQL_FetchInt(hResults,0);
    if(playercount>10)
        playercount=10;

    Format(buffer,sizeof(buffer),"SELECT * FROM thc_rpg ORDER BY level DESC, xp DESC, credits DESC LIMIT 10");
    hResults=SQL_Query(hSQL,buffer);
    SQL_FieldNameToNum(hResults,"name",column1);
    SQL_FieldNameToNum(hResults,"level",column2);
    for(new i=0;i<playercount;i++)
    {
        if(SQL_FetchRow(hResults))
        {
            SQL_FetchString(hResults, column1, Top10[i][0], maxlen);
            new level = SQL_FetchInt(hResults, column2);
            IntToString(level, Top10[i][1], maxlen);
        }
    }

    CloseHandle(hResults);
    return playercount;
}

CheckDBUpdate()
{
    new bool:error;
    decl String:errorbuffer[1024],String:query[64];
    new Handle:hResult=SQL_Query(hSQL,"SELECT dbver FROM thc_rpg");
    if(hResult)
    {
        SQL_Rewind(hResult);
        new bool:fetch=SQL_FetchRow(hResult);
        if(!fetch)
        {
            CloseHandle(hResult);
            LogMgr_Print(g_moduleCore, LogType_Error, "CheckDBUpdate", "Database query error, no data fetched");
            return 0;
        }
        else
        {
            new column;
            decl String:result[16];
            SQL_FieldNameToNum(hResult,"dbver",column);
            SQL_FetchString(hResult,column,result,sizeof(result));
            if(!SQL_IsFieldNull(hResult, column))
                if(StrEqual(result, PROJECT_VERSION, false))
                {
                    LogMgr_Print(g_moduleCore, LogType_Debug, "CheckDBUpdate", "Database version is uptodate, no changes needed");
                    return 0;
                }
                else
                {
                    // update database to actual version
                    Format(query,sizeof(query),"UPDATE thc_rpg SET dbver='%s'",PROJECT_VERSION);
                    SQL_FastQuery(hSQL,query);
                    LogMgr_Print(g_moduleCore, LogType_Debug, "CheckDBUpdate", "Database version updated to version '%s'", PROJECT_VERSION);
                    return 1;
                }
            else
                LogMgr_Print(g_moduleCore, LogType_Error, "CheckDBUpdate", "Database query error, field is NULL");
        }
    }
    else
    {
        error=SQL_GetError(hSQL, errorbuffer, sizeof(errorbuffer));
        if(error)
            LogMgr_Print(g_moduleCore, LogType_Error, "CheckDBUpdate", "Database query error, Reason: %s", errorbuffer);
        // column does not exist, create it!
        CreateColumn(hSQL,"dbver","VARCHAR(8)");
        // set dbver to actual project version
        Format(query,sizeof(query),"UPDATE thc_rpg SET dbver='%s'",PROJECT_VERSION);
        SQL_Query(hSQL,query);
        // check for errors
        error=SQL_GetError(hSQL, errorbuffer, sizeof(errorbuffer));
        if(error)
        {
            LogMgr_Print(g_moduleCore, LogType_Error, "CheckDBUpdate", "Database query error, Reason: %s", errorbuffer);
            return -1;
        }
        else
        {
            LogMgr_Print(g_moduleCore, LogType_Debug, "CheckDBUpdate", "Successfully created necessary column 'dbver' in database");
            return 1;
        }
    }

    return 1;
}
